#coding=utf-8
import os
import time
import pandas as pd
from sqlalchemy import create_engine
from line_profiler import LineProfiler, profile

lp = LineProfiler()

@profile
def excelToSql(path,host, user, password, database, table):
    engine = create_engine(f'mysql+pymysql://{user}:{password}@{host}:3306/{database}?charset=utf8')


    for root, dirs, files in os.walk(path):  # 第一个为起始路径，第二个为起始路径下的文件夹，第三个是起始路径下的文件。
        for file in files:
            start_time = time.perf_counter()
            file_path = os.path.join(root, file)  # 将路径名和文件名组合成一个完整路径
            print(file_path)
            # df = pd.read_excel(file_path,encoding="gbk") #excel转换成DataFrame
            df = pd.read_excel(file_path)  # excel转换成DataFrame
            # copyDf = df.loc[:, 'SUB_NO':'LAND_CODE'].copy()
            copyDf = df.loc[:, 'SUB_NO':'SLOPE'].copy()
            # copyDf['SUB_NO'] = copyDf.apply(lambda x: x['PGRID_NO'] + '_' +x['SUB_NO'] + '_' + x['GRID_TYPE'], axis=1)
            # copyDf.rename(columns={'LOW_LEFTLO': 'LOW_LEFT_LONG', 'LOW_LEFTLA': 'LOW_LEFT_LAT', 'UP_WEIGHLO': 'UP_RIGHT_LONG','UP_WEIGHLA':'UP_RIGHT_LAT','GRID_GEOM':'GEOM'}, inplace=True)
            copyDf.rename(columns={'LOW_LEFTLO': 'LOW_LEFT_LONG', 'LOW_LEFTLA': 'LOW_LEFT_LAT', 'UP_WEIGHLO': 'UP_RIGHT_LONG','UP_WEIGHLA':'UP_RIGHT_LAT'}, inplace=True)
            copyDf.to_sql(table, con=engine, if_exists='append', index=False)
            end_time = time.perf_counter()
            execution_time=end_time-start_time
            print(f"代码执行时间：{execution_time} 秒")



if __name__ == '__main__':
    # 使用示例
    # 使用示例
    # 使用示例
    path = r"E:\data\guagndong\excel3"
    host = "10.86.0.125"  # 数据库主机地址
    user = "root"  # 数据库用户名
    password = "Gh2024#Zny"  # 数据库密码
    database = "sfgc_kt"  # 数据库名称
    # todo:插入的数据库表
    table = "gis_subgrid_d_08"  # 数据库表名

    excelToSql(path, host, user, password, database, table)  # 调用函数，将Excel数据插入到MySQL数据库中
    lp.print_stats()